﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;


namespace ParseTimetable
{
    class ClassTime
    {
        private static string conStr = ConfigurationManager.ConnectionStrings["classTimeCon"].ConnectionString;

        public static void InsertClassTime(List<ClassTime> cts)
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {

                con.Open();
                SqlTransaction trans = con.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.Transaction = trans;
                try
                {

                    foreach (ClassTime ct in cts)
                    {
                        cmd.CommandText = "INSERT INTO ClassTime(className,yearIn,department,studyYear,majorName,mondayOne,mondayTwo,mondayThree,mondayFour,mondayFive,tuesdayOne,tuesdayTwo,tuesdayThree,tuesdayFour,tuesdayFive,wednesdayOne,wednesdayTwo,wednesdayThree,wednesdayFour,wednesdayFive,thursdayOne,thursdayTwo,thursdayThree,thursdayFour,thursdayFive,fridayOne,fridayTwo,fridayThree,fridayFour,fridayFive)  VALUES(@className,@yearIn,@department,@studyYear,@majorName,@mondayOne,@mondayTwo,@mondayThree,@mondayFour,@mondayFive,@tuesdayOne,@tuesdayTwo,@tuesdayThree,@tuesdayFour,@tuesdayFive,@wednesdayOne,@wednesdayTwo,@wednesdayThree,@wednesdayFour,@wednesdayFive,@thursdayOne,@thursdayTwo,@thursdayThree,@thursdayFour,@thursdayFive,@fridayOne,@fridayTwo,@fridayThree,@fridayFour,@fridayFive)";
                        cmd.Parameters.Add("@className", SqlDbType.VarChar, 100).Value = ct.className;
                        cmd.Parameters.Add("@yearIn", SqlDbType.VarChar, 100).Value = ct.yearIn;
                        cmd.Parameters.Add("@department", SqlDbType.VarChar, 100).Value = ct.department;
                        cmd.Parameters.Add("@studyYear", SqlDbType.VarChar, 100).Value = ct.studyYear;
                        cmd.Parameters.Add("@majorName", SqlDbType.VarChar, 100).Value = ct.majorName;
                        cmd.Parameters.Add("@mondayOne", SqlDbType.VarChar, 100).Value = ct.mondayOne == null ? "" : ct.mondayOne;
                        cmd.Parameters.Add("@mondayTwo", SqlDbType.VarChar, 100).Value = ct.mondayTwo == null ? "" : ct.mondayTwo;
                        cmd.Parameters.Add("@mondayThree", SqlDbType.VarChar, 100).Value = ct.mondayThree == null ? "" : ct.mondayThree;
                        cmd.Parameters.Add("@mondayFour", SqlDbType.VarChar, 100).Value = ct.mondayFour == null ? "" : ct.mondayFour;
                        cmd.Parameters.Add("@mondayFive", SqlDbType.VarChar, 100).Value = ct.mondayFive == null ? "" : ct.mondayFive;
                        cmd.Parameters.Add("@tuesdayOne", SqlDbType.VarChar, 100).Value = ct.tuesdayOne == null ? "" : ct.tuesdayOne;
                        cmd.Parameters.Add("@tuesdayTwo", SqlDbType.VarChar, 100).Value = ct.tuesdayTwo == null ? "" : ct.tuesdayTwo;
                        cmd.Parameters.Add("@tuesdayThree", SqlDbType.VarChar, 100).Value = ct.tuesdayThree == null ? "" : ct.tuesdayThree;
                        cmd.Parameters.Add("@tuesdayFour", SqlDbType.VarChar, 100).Value = ct.tuesdayFour == null ? "" : ct.tuesdayFour;
                        cmd.Parameters.Add("@tuesdayFive", SqlDbType.VarChar, 100).Value = ct.tuesdayFive == null ? "" : ct.tuesdayFive;
                        cmd.Parameters.Add("@wednesdayOne", SqlDbType.VarChar, 100).Value = ct.wednesdayOne == null ? "" : ct.wednesdayOne;
                        cmd.Parameters.Add("@wednesdayTwo", SqlDbType.VarChar, 100).Value = ct.wednesdayTwo == null ? "" : ct.wednesdayTwo;
                        cmd.Parameters.Add("@wednesdayThree", SqlDbType.VarChar, 100).Value = ct.wednesdayThree == null ? "" : ct.wednesdayThree;
                        cmd.Parameters.Add("@wednesdayFour", SqlDbType.VarChar, 100).Value = ct.wednesdayFour == null ? "" : ct.wednesdayFour;
                        cmd.Parameters.Add("@wednesdayFive", SqlDbType.VarChar, 100).Value = ct.wednesdayFive == null ? "" : ct.wednesdayFive;
                        cmd.Parameters.Add("@thursdayOne", SqlDbType.VarChar, 100).Value = ct.thursdayOne == null ? "" : ct.thursdayOne;
                        cmd.Parameters.Add("@thursdayTwo", SqlDbType.VarChar, 100).Value = ct.thursdayTwo == null ? "" : ct.thursdayTwo;
                        cmd.Parameters.Add("@thursdayThree", SqlDbType.VarChar, 100).Value = ct.thursdayThree == null ? "" : ct.thursdayThree;
                        cmd.Parameters.Add("@thursdayFour", SqlDbType.VarChar, 100).Value = ct.thursdayFour == null ? "" : ct.thursdayFour;
                        cmd.Parameters.Add("@thursdayFive", SqlDbType.VarChar, 100).Value = ct.thursdayFive == null ? "" : ct.thursdayFive;
                        cmd.Parameters.Add("@fridayOne", SqlDbType.VarChar, 100).Value = ct.fridayOne == null ? "" : ct.fridayOne;
                        cmd.Parameters.Add("@fridayTwo", SqlDbType.VarChar, 100).Value = ct.fridayTwo == null ? "" : ct.fridayTwo;
                        cmd.Parameters.Add("@fridayThree", SqlDbType.VarChar, 100).Value = ct.fridayThree == null ? "" : ct.fridayThree;
                        cmd.Parameters.Add("@fridayFour", SqlDbType.VarChar, 100).Value = ct.fridayFour == null ? "" : ct.fridayFour;
                        cmd.Parameters.Add("@fridayFive", SqlDbType.VarChar, 100).Value = ct.fridayFive == null ? "" : ct.fridayFive;
                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    trans.Commit();
                }
                catch (Exception)
                {
                    trans.Rollback();
                }
                finally
                {
                    con.Close();
                }
                


            }
        }
        //public static void InsertClassTime(List<ClassTime> cts)
        //{
        //    DataTable dt = new DataTable();
        //    dt.Columns.Add("ID", typeof(int));
        //    dt.Columns.Add("className", typeof(string));
        //    dt.Columns.Add("department", typeof(string)); 
        //    dt.Columns.Add("studyYear", typeof(string));
        //    dt.Columns.Add("majorName", typeof(string));
        //    dt.Columns.Add("mondayOne", typeof(string));
        //    dt.Columns.Add("mondayTwo", typeof(string));
        //    dt.Columns.Add("mondayThree", typeof(string));
        //    dt.Columns.Add("mondayFour", typeof(string));
        //    dt.Columns.Add("mondayFive", typeof(string));
        //    dt.Columns.Add("tuesdayOne", typeof(string));
        //    dt.Columns.Add("tuesdayTwo", typeof(string));
        //    dt.Columns.Add("tuesdayThree", typeof(string));
        //    dt.Columns.Add("tuesdayFour", typeof(string));
        //    dt.Columns.Add("tuesdayFive", typeof(string));
        //    dt.Columns.Add("wednesdayOne", typeof(string));
        //    dt.Columns.Add("wednesdayTwo", typeof(string));
        //    dt.Columns.Add("wednesdayThree", typeof(string));
        //    dt.Columns.Add("wednesdayFour", typeof(string));
        //    dt.Columns.Add("wednesdayFive", typeof(string));
        //    dt.Columns.Add("thursdayOne", typeof(string));
        //    dt.Columns.Add("thursdayTwo", typeof(string));
        //    dt.Columns.Add("thursdayThree", typeof(string));
        //    dt.Columns.Add("thursdayFour", typeof(string));
        //    dt.Columns.Add("thursdayFive", typeof(string));
        //    dt.Columns.Add("fridayOne", typeof(string));
        //    dt.Columns.Add("fridayTwo", typeof(string));
        //    dt.Columns.Add("fridayThree", typeof(string));
        //    dt.Columns.Add("fridayFour", typeof(string));
        //    dt.Columns.Add("fridayFive", typeof(string));
        //    for (int i = 0; i <cts.Count ; i++)
        //    {
        //        DataRow r = dt.NewRow();
                
        //        r["className"] = cts[i].className;
        //        r["department"] = cts[i].department;
        //        r["studyYear"] = cts[i].studyYear;
        //        r["majorName"] = cts[i].majorName;
        //        r["mondayOne"] = cts[i].mondayOne;
        //        r["mondayTwo"] = cts[i].mondayTwo;
        //        r["mondayThree"] = cts[i].mondayThree;
        //        r["mondayFour"] = cts[i].mondayFour;
        //        r["mondayFive"] = cts[i].mondayFive;
        //        r["tuesdayOne"] = cts[i].tuesdayOne;
        //        r["tuesdayTwo"] = cts[i].tuesdayTwo;
        //        r["tuesdayThree"] = cts[i].tuesdayThree;
        //        r["tuesdayFour"] = cts[i].tuesdayFour;
        //        r["tuesdayFive"] = cts[i].tuesdayFive;
        //        r["wednesdayOne"] = cts[i].wednesdayOne;
        //        r["wednesdayTwo"] = cts[i].wednesdayTwo;
        //        r["wednesdayThree"] = cts[i].wednesdayThree;
        //        r["wednesdayFour"] = cts[i].wednesdayFour;
        //        r["wednesdayFive"] = cts[i].wednesdayFive;
        //        r["thursdayOne"] = cts[i].thursdayOne;
        //        r["thursdayTwo"] = cts[i].thursdayTwo;
        //        r["thursdayThree"] = cts[i].thursdayThree;
        //        r["thursdayFour"] = cts[i].thursdayFour;
        //        r["thursdayFive"] = cts[i].thursdayFive;
        //        r["fridayOne"] = cts[i].fridayOne;
        //        r["fridayTwo"] = cts[i].fridayTwo;
        //        r["fridayThree"] = cts[i].fridayThree;
        //        r["fridayFour"] = cts[i].fridayFour;
        //        r["fridayFive"] = cts[i].fridayFive;
        //        dt.Rows.Add(r);
        //    }

        //    ExecuteTransactionScopeInsertEx(dt, "ClassTime");
       // }

        //public static void ExecuteTransactionScopeInsertEx(DataTable dt, string tableName)
        //{
        //    using (SqlConnection con = new SqlConnection(conStr))
        //    {
        //        con.Open();
        //        using (SqlBulkCopy sbc = new SqlBulkCopy(conStr, SqlBulkCopyOptions.UseInternalTransaction))
        //        {
        //            try
        //            {
        //                //服务器上目标表的名称   
        //                sbc.DestinationTableName = tableName;
        //                sbc.BatchSize = dt.Rows.Count;
        //                sbc.BulkCopyTimeout = 10000;

        //                for (int i = 0, l = dt.Columns.Count; i < l; i++)
        //                {
        //                    //列映射定义数据源中的列和目标表中的列之间的关系   
        //                    sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
        //                }
                        
        //                sbc.WriteToServer(dt);
        //            }
        //            catch (Exception e)
        //            {
        //                MessageBox.Show(e.Message);
        //            }
        //            finally
        //            {
        //                sbc.Close();
        //                con.Close();
        //            }

        //        }
        //    }
           
        //}

        public ClassTime( string className,string yearIn, string department, string studyYear, string majorName)
        {
            
            this.className = className;
            this.yearIn = yearIn;
            this.department = department;
            this.studyYear = studyYear;
            this.majorName = majorName;
        }
        private int id;

        public int Id
        {
            get { return id; }
           
        }
        private string className;

        public string ClassName
        {
            get { return className; }
            set { className = value; }
        }

        private string yearIn;

        public string YearIn
        {
            get { return yearIn; }
            set { yearIn = value; }
        }
        private string department;

        public string Department
        {
            get { return department; }
            set { department = value; }
        }
        private string studyYear;

        public string StudyYear
        {
            get { return studyYear; }
            set { studyYear = value; }
        }
        private string majorName;

        public string MajorName
        {
            get { return majorName; }
            set { majorName = value; }
        }
        private string mondayOne;

        public string MondayOne
        {
            get { return mondayOne; }
            set { mondayOne = value; }
        }
        private string mondayTwo;

        public string MondayTwo
        {
            get { return mondayTwo; }
            set { mondayTwo = value; }
        }
        private string mondayThree;

        public string MondayThree
        {
            get { return mondayThree; }
            set { mondayThree = value; }
        }
        private string mondayFour;

        public string MondayFour
        {
            get { return mondayFour; }
            set { mondayFour = value; }
        }
        private string mondayFive;

        public string MondayFive
        {
            get { return mondayFive; }
            set { mondayFive = value; }
        }
        private string tuesdayOne;

        public string TuesdayOne
        {
            get { return tuesdayOne; }
            set { tuesdayOne = value; }
        }
        private string tuesdayTwo;

        public string TuesdayTwo
        {
            get { return tuesdayTwo; }
            set { tuesdayTwo = value; }
        }
        private string tuesdayThree;

        public string TuesdayThree
        {
            get { return tuesdayThree; }
            set { tuesdayThree = value; }
        }
        private string tuesdayFour;

        public string TuesdayFour
        {
            get { return tuesdayFour; }
            set { tuesdayFour = value; }
        }
        private string tuesdayFive;

        public string TuesdayFive
        {
            get { return tuesdayFive; }
            set { tuesdayFive = value; }
        }
        private string wednesdayOne;

        public string WednesdayOne
        {
            get { return wednesdayOne; }
            set { wednesdayOne = value; }
        }
        private string wednesdayTwo;

        public string WednesdayTwo
        {
            get { return wednesdayTwo; }
            set { wednesdayTwo = value; }
        }
        private string wednesdayThree;

        public string WednesdayThree
        {
            get { return wednesdayThree; }
            set { wednesdayThree = value; }
        }
        private string wednesdayFour;

        public string WednesdayFour
        {
            get { return wednesdayFour; }
            set { wednesdayFour = value; }
        }
        private string wednesdayFive;

        public string WednesdayFive
        {
            get { return wednesdayFive; }
            set { wednesdayFive = value; }
        }
        private string thursdayOne;

        public string ThursdayOne
        {
            get { return thursdayOne; }
            set { thursdayOne = value; }
        }
        private string thursdayTwo;

        public string ThursdayTwo
        {
            get { return thursdayTwo; }
            set { thursdayTwo = value; }
        }
        private string thursdayThree;

        public string ThursdayThree
        {
            get { return thursdayThree; }
            set { thursdayThree = value; }
        }
        private string thursdayFour;

        public string ThursdayFour
        {
            get { return thursdayFour; }
            set { thursdayFour = value; }
        }
        private string thursdayFive;

        public string ThursdayFive
        {
            get { return thursdayFive; }
            set { thursdayFive = value; }
        }
        private string fridayOne;

        public string FridayOne
        {
            get { return fridayOne; }
            set { fridayOne = value; }
        }
        private string fridayTwo;

        public string FridayTwo
        {
            get { return fridayTwo; }
            set { fridayTwo = value; }
        }
        private string fridayThree;

        public string FridayThree
        {
            get { return fridayThree; }
            set { fridayThree = value; }
        }
        private string fridayFour;

        public string FridayFour
        {
            get { return fridayFour; }
            set { fridayFour = value; }
        }
        private string fridayFive;

        public string FridayFive
        {
            get { return fridayFive; }
            set { fridayFive = value; }
        }
    }
}
